package com.gxuwz.attend.dao;

import com.gxuwz.attend.entity.Leave;
import com.gxuwz.attend.tools.DateUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class LeaveDao {
    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    /**
     * 添加请假数据
     * @param leave
     */
    public void insertLeave(Leave leave){
        String sql = "insert into sys_leave (leaveID,courseID,reason,daynum,stuNo,applytime,status,datetime,opinion)values(?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,leave.getLeaveID(),leave.getCourseID(),leave.getReason(),leave.getDaynum(),leave.getStuNo(), leave.getApplytime(),leave.getStatus(),leave.getDatetime(),leave.getOpinion());
    }
    /**
     * 删除请假数据
     * @param leaveID
     */
    public void deleteLeave(String leaveID){
        String sql = "delete from sys_leave where leaveID=?";
        jdbcTemplate.update(sql,leaveID);
    }
    /**
     * 更新请假数据
     * @param leave
     */
    public void updateLeave(Leave leave){
        String sql = "update sys_leave set courseID=?,reason=?,daynum=?,stuNo=?,applytime=?,status=?,datetime=?,opinion=? where leaveID=?";
        jdbcTemplate.update(sql,leave.getCourseID(),leave.getReason(),leave.getDaynum(),leave.getStuNo(), leave.getApplytime(),leave.getStatus(),leave.getDatetime(),leave.getOpinion(),leave.getLeaveID());
    }
    /**
     * 查询全部请假数据
     * @return
     */
    public List<Leave> queryLeaves(){
        String sql = "select * from sys_leave where 1=1";
        List<Object> paramValues = new ArrayList<>();
        List<Leave> leaveList = new ArrayList<>();
        leaveList = jdbcTemplate.query(sql, paramValues.toArray(), new RowMapper<Leave>() {
            @Override
            public Leave mapRow(ResultSet resultSet, int i) throws SQLException {
                Leave leave1 = new Leave();
                String leaveID = resultSet.getString("leaveID");
                String courseID = resultSet.getString("courseID");
                String reason = resultSet.getString("reason");
                int daynum = resultSet.getInt("daynum");
                String stuNo = resultSet.getString("stuNo");
                //System.out.println("applytime:"+resultSet.getString("applytime"));
                //调用方法处理日期后赋值
                String applytimeStr = resultSet.getString("applytime");
                LocalDate applytime=null;
                if (applytimeStr!=null){
                    applytime= DateUtil.now(applytimeStr);
                }
                String status = resultSet.getString("status");

                // 将 datetime 字段从字符串转换为 LocalDate
                String datetimeStr = resultSet.getString("datetime");
                LocalDate datetime = null;
                if (datetimeStr!=null){
                    datetime= DateUtil.now(datetimeStr);
                }

                String opinion = resultSet.getString("opinion");
                leave1.setLeaveID(leaveID);
                leave1.setCourseID(courseID);
                leave1.setReason(reason);
                leave1.setDaynum(daynum);
                leave1.setStuNo(stuNo);
                leave1.setApplytime(applytime);
                leave1.setStatus(status);
                leave1.setDatetime(datetime);
                leave1.setOpinion(opinion);
                return leave1;
            }
        });
        return leaveList;
    }
    /**
     * 模糊查询请假数据
     * @param leave
     * @return
     */
    public List<Leave> queryLeave(Leave leave){
        String sql = "select * from sys_leave where 1=1";
        List<Object> paramValues = new ArrayList<>();
        List<Leave> leaveList = new ArrayList<>();
        if(leave!=null){
            if(leave.getLeaveID()!=null&&!"".equals(leave.getLeaveID().trim())){
                sql += " and leaveID=?";
                paramValues.add(leave.getLeaveID());
            }
            if(leave.getCourseID()!=null&&!"".equals(leave.getCourseID().trim())){
                sql += " and courseID=?";
                paramValues.add(leave.getCourseID());
            }
            if(leave.getReason()!=null&&!"".equals(leave.getReason().trim())){
                sql += " and reason=?";
                paramValues.add(leave.getReason());
            }
            if(leave.getDaynum()!=null&&!"".equals(leave.getDaynum())){
                sql += " and daynum=?";
                paramValues.add(leave.getDaynum());
            }
            if(leave.getStuNo()!=null&&!"".equals(leave.getStuNo().trim())){
                sql += " and stuNo=?";
                paramValues.add(leave.getStuNo());
            }
            if(leave.getApplytime()!=null&&!"".equals(leave.getApplytime())){
                sql += " and applytime=?";
                paramValues.add(leave.getApplytime());
            }
            if(leave.getStatus()!=null&&!"".equals(leave.getStatus().trim())){
                sql += " and status=?";
                paramValues.add(leave.getStatus());
            }
            if(leave.getDatetime()!=null&&!"".equals(leave.getDatetime())){
                sql += " and datetime=?";
                paramValues.add(leave.getDatetime());
            }
            if(leave.getOpinion()!=null&&!"".equals(leave.getOpinion().trim())){
                sql += " and opinion=?";
                paramValues.add(leave.getOpinion());
            }
        }
        leaveList = jdbcTemplate.query(sql, paramValues.toArray(), new RowMapper<Leave>() {
            @Override
            public Leave mapRow(ResultSet resultSet, int i) throws SQLException {
                Leave leave1 = new Leave();
                String leaveID = resultSet.getString("leaveID");
                String courseID = resultSet.getString("courseID");
                String reason = resultSet.getString("reason");
                int daynum = resultSet.getInt("daynum");
                String stuNo = resultSet.getString("stuNo");
                //System.out.println("applytime:"+resultSet.getString("applytime"));
                //调用方法处理日期后赋值
                String applytimeStr = resultSet.getString("applytime");
                LocalDate applytime=null;
                if (applytimeStr!=null){
                    applytime= DateUtil.now(applytimeStr);
                }
                String status = resultSet.getString("status");

                // 将 datetime 字段从字符串转换为 LocalDate
                String datetimeStr = resultSet.getString("datetime");
                LocalDate datetime = null;
                if (datetimeStr!=null){
                    datetime= DateUtil.now(datetimeStr);
                }

                String opinion = resultSet.getString("opinion");
                leave1.setLeaveID(leaveID);
                leave1.setCourseID(courseID);
                leave1.setReason(reason);
                leave1.setDaynum(daynum);
                leave1.setStuNo(stuNo);
                leave1.setApplytime(applytime);
                leave1.setStatus(status);
                leave1.setDatetime(datetime);
                leave1.setOpinion(opinion);
                return leave1;
            }
        });
        return leaveList;
    }
}
